﻿/*  Copyright (C) 2012 Jeremy Liner

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Security.Cryptography;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

namespace PG_Lottery
{
    /// <summary>
    /// PGLotteryForm – Tool used to create a lottery and generate random results
    /// The user will need to input the string for the input file path
    /// Once the input string is validated the user can populate the input dataset using Open File button
    /// The input dataset can be used to generate the tickets
    /// Once the tickets have been generated, the user can select tickets randomly
    /// Once a ticket has been selected, the last ticket's user can be removed from the ticket dataset
    /// </summary>
    /// <remarks>
    /// Uses 2 datasets to pull in data and display to the user.
    /// Generates a log of each winner, located where the form is executed from.
    /// The random number is generated with the RNGCryptoServiceProvider
    /// </remarks>
    /// <notes>
    /// History 
    /// -------
    /// mm/dd/yyyy	Name	        Change Description
    /// 01/20/2012  Jeremy Liner    Initial Build
    /// 05/12/2012  Jeremy Liner    Additional Functionality: Excel input, internal column mapping, CSV input, Add entry to live lottery
    /// </notes>
    public partial class PGLotteryForm : Form
    {
        #region Members
        private DataSet dsFileData;
        private DataSet dsLotteryData;
        //private DataView dtvFileData;
        //private DataView dtvLotteryData;

        RNGCryptoServiceProvider _rngCsp;
        private int _result;
        private int _lowerBound;
        private int _upperBound;

        private bool readyToRemove;

        //name of the log file
        const string LOGFILE = "PGLottery_Log.txt";
        const string LOGFILEWINNERS = "PGLottery_Winner_Log.txt";

        //name of the input dataset columns
        const string STUDENT = "Student";
        const string TICKETS = "Tickets";
        
        //name of the output dataset columns
        const string LOTTERYDATA = "LotteryData";
        const string NAME = "Name";
        const string TICKET = "Tickets";
        const string ID = "ID";

        string nameMap = "Name";
        string ticketMap = "Tickets";
        string idMap = "ID";
        #endregion
        
        #region Methods
        public PGLotteryForm()
        {
            InitializeComponent();

            _rngCsp = new RNGCryptoServiceProvider();
            _result = 0;
            _lowerBound = 0;
            _upperBound = 0;
            readyToRemove = false;

            dsFileData = new DataSet();
            dsLotteryData = new DataSet();

            dsLotteryData.Tables.Add(LOTTERYDATA);
            dsLotteryData.Tables[LOTTERYDATA].Columns.Add(NAME, typeof(string));
            dsLotteryData.Tables[LOTTERYDATA].Columns.Add(TICKET, typeof(int));
            dsLotteryData.Tables[LOTTERYDATA].Columns.Add(ID, typeof(string));

            dgvInputData.AutoGenerateColumns = true;
            dgvLotteryData.AutoGenerateColumns = true;
        }

        #region Form Methods
        private void btnBrowse_Click(object sender, EventArgs e)
        {
            txtStatus.Text = "Opening File Location";
            ofdInput.ShowDialog(this);
        }

        private void ofdInput_FileOk(object sender, CancelEventArgs e)
        {
            if (!string.IsNullOrEmpty(ofdInput.FileName))
            {
                txtInput.Text = ofdInput.FileName;

                txtStatus.Text = "File Location Set, Press Open File to load Input Data.";
            }
        }
        
        private void btnOpenFile_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtInput.Text))
            {
                if (File.Exists(txtInput.Text))
                {
                    FileInfo fi = new FileInfo(txtInput.Text);
                    string extention = fi.Extension.ToLower();

                    switch(extention)
                    {
                        case ".xml" :
                            getFileDataXML();
                            break;
                        case ".xls" :
                            getFileDataExcel();
                            break;
                        case ".xlsx" :
                            getFileDataExcel();
                            break;
                        case ".csv" :
                            getFileDataCSV(true);
                            break;
                        default:
                            break;                        
                    }
                }

            }
            else
            {
                txtStatus.Text = "No File Specified";
            }
        }

        private void btnNewLottery_Click(object sender, EventArgs e)
        {
            try
            {
                //Clear the current entry selected (if there is one)
                txtOutput.Text = string.Empty;
                //Prevent an entry from old data (that no longer exists) being removed in the new data
                readyToRemove = false;

                //Remove old data
                dsLotteryData.Clear();

                // Set to null
                dgvLotteryData.DataSource = null;
                // Clear Data
                dgvLotteryData.Rows.Clear();
                // All Data Associations
                dgvLotteryData.DataSource = dsLotteryData.Tables[LOTTERYDATA];

                populateLotteryData();

                txtStatus.Text = "Tickets generated Successfully";

                //Log remove user
                using (StreamWriter logFile = File.AppendText(LOGFILE))
                {
                    logFile.WriteLine(string.Format("Populated Lottery Data - Time: {0}", DateTime.Now.ToString()));
                    logFile.Flush();
                }
            }
            catch (Exception ex)
            {
                txtStatus.Text = "Error: " + ex.Message;
            }
        }

        private void btnUpdateLottery_Click(object sender, EventArgs e)
        {
            try
            {
                //Clear the current entry selected (if there is one)
                txtOutput.Text = string.Empty;
                //Prevent an entry from old data (that no longer exists) being removed in the new data
                readyToRemove = false;
                
                populateLotteryData();

                txtStatus.Text = "Tickets Added Successfully";

                //Log remove user
                using (StreamWriter logFile = File.AppendText(LOGFILE))
                {
                    logFile.WriteLine(string.Format("Updated Lottery Data - Time: {0}", DateTime.Now.ToString()));
                    logFile.Flush();
                }
            }
            catch (Exception ex)
            {
                txtStatus.Text = "Error: " + ex.Message;
            }
        }
        
        private void btnSelectRandom_Click(object sender, EventArgs e)
        {
            try
            {
                txtLotteryStatus.Text = "";
                if (_upperBound > 0)
                {
                    //Get 4 Bytes worth of information from the random number generator (it returns in Bytes)
                    Byte[] randomNumber = new Byte[4];
                    _rngCsp.GetBytes(randomNumber);

                    //Convert the Byte Array to an unsigned integer (non-negative)
                    UInt32 randomInt = BitConverter.ToUInt32(randomNumber, 0);
                    //Convert the random number into a number between the lower and upper bound
                    randomInt = Convert.ToUInt32((randomInt % _upperBound) + _lowerBound);
                    _result = Convert.ToInt32(randomInt);
                    txtOutput.Text = randomInt.ToString();

                    // display the random number and winners name
                    DataTable dtLotteryData = dsLotteryData.Tables[LOTTERYDATA];
                    DataRow row = dtLotteryData.Rows[_result];
                    txtOutput.Text = string.Format("Number: {0},Name: {1}, ID: {2}", row[TICKET], row[NAME], row[ID]);

                    readyToRemove = true;
                    //removeRows(NAME, row[NAME].ToString());

                    //Log the winner
                    using (StreamWriter logFile = File.AppendText(LOGFILE))
                    {
                        logFile.WriteLine(string.Format("Winner Number: {0},Name: {1}, ID: {2}, Time: {3}", row[TICKET], row[NAME], row[ID], DateTime.Now.ToString()));
                        logFile.Flush();
                    }
                    //Log the winner
                    using (StreamWriter logFile = File.AppendText(LOGFILEWINNERS))
                    {
                        logFile.WriteLine(string.Format("Number: {0},Name: {1}, ID: {2}, Time: {3}", row[TICKET], row[NAME], row[ID], DateTime.Now.ToString()));
                        logFile.Flush();
                    }
                }
                else 
                {
                    txtLotteryStatus.Text = "No tickets to select from";
                }
            }
            catch(Exception ex)
            {
                txtLotteryStatus.Text = "Error: " + ex.Message;
            }
        }

        private void btnRemoveRow_Click(object sender, EventArgs e)
        {
            txtLotteryStatus.Text = "";
            //if a user has been selected to win, that user can be removed
            if (readyToRemove)
            {
                //Get the row to be removed
                DataTable dtLotteryData = dsLotteryData.Tables[LOTTERYDATA];
                DataRow row = dtLotteryData.Rows[_result];

                //Log remove user
                using (StreamWriter logFile = File.AppendText(LOGFILE))
                {
                    logFile.WriteLine(string.Format("Removed Name: {0}, ID: {1}, Time: {2}", row[NAME], row[ID], DateTime.Now.ToString()));
                    logFile.Flush();
                }

                //remove row
                removeRows(NAME, row[NAME].ToString());

                //after a row has been removed, do not allow the row to be removed until another row has been selected
                readyToRemove = false;
            }
            else 
            {
                txtLotteryStatus.Text = "Noone selected to remove";
            }
        }

        private void btnAddTickets_Click(object sender, EventArgs e)
        {
            try
            {
                //Validate input
                if ((!string.IsNullOrEmpty(txtIDSingleInput.Text)) && (!string.IsNullOrEmpty(txtNameSingleInput.Text)) && (!string.IsNullOrEmpty(txtTicketsSingleInput.Text)))
                {
                    //Add entries to lottery
                    addEntryToLotteryData();
                }
            }
            catch (Exception ex)
            {
                txtLotteryStatus.Text = "Error: " + ex.Message;
            }
        }

        private void txtIDMap_TextChanged(object sender, EventArgs e)
        {
            idMap = txtIDMap.Text;
        }
        
        private void txtNameMap_TextChanged(object sender, EventArgs e)
        {
            nameMap = txtNameMap.Text;
        }        

        private void txtTicketMap_TextChanged(object sender, EventArgs e)
        {
            ticketMap = txtTicketMap.Text;
        }
        #endregion

        #region Private Methods
        #region Data Input
        private void getFileDataXML()
        {
            try
            {
                //Check if file path is empty
                if (!string.IsNullOrEmpty(txtInput.Text))
                {   
                    //check if file exists at file path location
                    if (File.Exists(txtInput.Text))
                    {
                        txtStatus.Text = "Loading Data....";
                        //clear the current dataset
                        dsFileData.Clear();
                        dsFileData.Tables.Clear();
                        dsFileData.ReadXml(txtInput.Text);
                        //dtvFileData = new DataView(dsFileData.Tables[STUDENTS]);

                        // Set to null
                        dgvInputData.DataSource = null;
                        // Clear Data
                        dgvInputData.Rows.Clear();
                        // All Data Associations
                        dgvInputData.DataSource = dsFileData.Tables[STUDENT];

                        //Set the mapping for columns
                        txtNameMap.Text = "Name";
                        nameMap = "Name";
                        txtIDMap.Text = "ID";
                        idMap = "ID";
                        txtTicketMap.Text = "Tickets";
                        ticketMap = "Tickets";

                        txtStatus.Text = "Data Loaded Successfully, Press Update Lottery to generate tickets.";
                    }
                }
            }
            catch (Exception ex)
            {
                txtStatus.Text = "Error: " + ex.Message;
            }
        }

        // Thank you to http://www.dotneter.com/get-excel-sheet-names-using-c for this little gem
        private void getFileDataExcel()
        {
            try
            {
                string connectionString = string.Empty;
                //check if we are working with excel 2010 or before
                if (Path.GetExtension(txtInput.Text) == ".xlsx") 
                {
                    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + txtInput.Text + ";Extended Properties=Excel 12.0;"; 
                } 
                else 
                {
                    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txtInput.Text + ";Extended Properties=Excel 8.0;"; 
                } 

                //get the sheet names in the workbook
                List<string> SheetNames = GetSheetNames(txtInput.Text);
                //use the first sheet in the workbook
                string sheetName = SheetNames[0];

                //clear the current dataset
                dsFileData.Clear();
                dsFileData.Tables.Clear();

                //create an adapter to the excel workbook that will get all the data from a sheet
                string query = String.Format("select * from [{0}]", sheetName);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, connectionString);
                
                //fill the dataset with the excel data
                dataAdapter.Fill(dsFileData);
                dsFileData.Tables[0].TableName = STUDENT;
                dgvInputData.DataSource = dsFileData.Tables[0];
                
                //Set the mapping for columns
                txtNameMap.Text = "Name";
                nameMap = "Name";
                txtIDMap.Text = "ID";
                idMap = "ID";
                txtTicketMap.Text = "Tickets";
                ticketMap = "Tickets";

                //special case for excel document output of PG info
                if (dsFileData.Tables[0].Columns.Contains("CompanyName"))
                {
                    txtNameMap.Text = "CompanyName";
                    nameMap = "CompanyName";
                }
                if (dsFileData.Tables[0].Columns.Contains("Text17"))
                {
                    txtIDMap.Text = "Text17";
                    idMap = "Text17";
                }
                if (dsFileData.Tables[0].Columns.Contains("CountOfRaffleTicketNumber"))
                {
                    txtTicketMap.Text = "CountOfRaffleTicketNumber";
                    ticketMap = "CountOfRaffleTicketNumber";
                }

            } 
            catch
            { 
            } 

        }

        public static List<string> GetSheetNames(string path)
        {
            List<string> sheets = new List<string>();
            string connectionString = string.Empty;

            if (Path.GetExtension(path) == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
            }
            else
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=Excel 8.0;";
            }

            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = connectionString;
            connection.Open();
            DataTable tbl = connection.GetSchema("Tables");
            connection.Close();
            foreach (DataRow row in tbl.Rows)
            {
                string sheetName = (string)row["TABLE_NAME"];
                //not required anymore
                //if (sheetName.EndsWith("$"))
                //{
                //    sheetName = sheetName.Substring(0, sheetName.Length - 1);
                //}
                sheets.Add(sheetName);
            }
            return sheets;
        }

        //Thank you to Jim Scott http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable for CSV upload
        private void getFileDataCSV(bool isFirstRowHeader)
        {                        
            try
            {
                //Check if file path is empty
                if (!string.IsNullOrEmpty(txtInput.Text))
                {   
                    //check if file exists at file path location
                    if (File.Exists(txtInput.Text))
                    {
                        txtStatus.Text = "Loading Data....";
                        dsFileData.Clear();
                        dsFileData.Tables.Clear();
                        dsFileData.Tables.Add(STUDENT);

                        string header = isFirstRowHeader ? "Yes" : "No";
                        FileInfo fi = new FileInfo(txtInput.Text);
                        string pathOnly = Path.GetDirectoryName(txtInput.Text);
                        string fileName = Path.GetFileName(txtInput.Text);

                        string sql = @"SELECT * FROM [" + fileName + "]";

                        using (OleDbConnection connection = new OleDbConnection(
                                  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly +
                                  ";Extended Properties=\"Text;HDR=" + header + "\""))
                        using (OleDbCommand command = new OleDbCommand(sql, connection))
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            adapter.Fill(dsFileData.Tables[STUDENT]);
                        } 

                        // Set to null
                        dgvInputData.DataSource = null;
                        // Clear Data
                        dgvInputData.Rows.Clear();
                        // All Data Associations
                        dgvInputData.DataSource = dsFileData.Tables[STUDENT];

                        //Set the mapping for columns
                        txtNameMap.Text = "Name";
                        nameMap = "Name";
                        txtIDMap.Text = "ID";
                        idMap = "ID";
                        txtTicketMap.Text = "Tickets";
                        ticketMap = "Tickets";

                        txtStatus.Text = "Data Loaded Successfully, Press Update Lottery to generate tickets.";
                    }
                }
            }
            catch (Exception ex)
            {
                txtStatus.Text = "Error: " + ex.Message;
            }
        }

        #endregion

        #region Lottery Functions
        private void getTotalTickets()
        {           
            //Set the upper bound of the random number
            _upperBound = dgvLotteryData.RowCount - 1;

            //display the total tickets
            lblCount.Text = _upperBound.ToString();
        }

        /// <summary>
        /// Populates the data in the lottery from the dataset in the datagridview.
        /// </summary>
        private void populateLotteryData()
        {
            try
            {
                DataTable tblLottery = dsLotteryData.Tables[LOTTERYDATA];
                // Iterate through every row of the Input Data
                foreach (DataGridViewRow row in dgvInputData.Rows)
                {
                    // For every row that isnt the new row (empty row)
                    if (!row.IsNewRow)
                    {
                        try
                        {
                            if ((!string.IsNullOrEmpty(row.Cells[ticketMap].Value.ToString())) && (!string.IsNullOrEmpty(row.Cells[nameMap].Value.ToString())) && (!string.IsNullOrEmpty(row.Cells[idMap].Value.ToString())))
                            {
                                for (int i = 0; i < Convert.ToInt32(row.Cells[ticketMap].Value); i++)
                                {
                                    tblLottery.Rows.Add(row.Cells[nameMap].Value, dgvLotteryData.RowCount, row.Cells[idMap].Value);
                                }
                            }
                        }
                        catch (Exception ex)
                        {
                            txtStatus.Text = "Error: " + ex.Message;
                        }
                    }
                }

                //Set the upper bound of the random number
                //If using random number, upper bound is inclusive
                //_upperBound = dgvLotteryData.RowCount - 1;
                //If using random number generator crypto service provider (modulo), upper bound is exclusive
                getTotalTickets();
            }
            catch(Exception ex)
            {
                txtStatus.Text = "Error: " + ex.Message;
            }
        }

        private void addEntryToLotteryData()
        {
            try
            {
                DataTable tblLottery = dsLotteryData.Tables[LOTTERYDATA];

                try
                {
                    if ((!string.IsNullOrEmpty(txtIDSingleInput.Text)) && (!string.IsNullOrEmpty(txtNameSingleInput.Text)) && (!string.IsNullOrEmpty(txtTicketsSingleInput.Text)))
                    {
                        for (int i = 0; i < Convert.ToInt32(txtTicketsSingleInput.Text); i++)
                        {
                            tblLottery.Rows.Add(txtNameSingleInput.Text, dgvLotteryData.RowCount, txtIDSingleInput.Text);
                        }
                    }

                    //output to status bar
                    txtLotteryStatus.Text = string.Format("Added {0} new tickets for {1}", txtTicketsSingleInput.Text, txtNameSingleInput.Text);

                    //Log remove user
                    using (StreamWriter logFile = File.AppendText(LOGFILE))
                    {
                        logFile.WriteLine(string.Format("Added Name: {0}, ID: {1}, Tickets: {2}, Time: {3}", txtNameSingleInput.Text, txtIDSingleInput.Text, txtTicketsSingleInput.Text, DateTime.Now.ToString()));
                        logFile.Flush();
                    }
                }
                catch (Exception ex)
                {
                    txtLotteryStatus.Text = "Error: Ticket value must be a number. " + ex.Message;
                }

                getTotalTickets();
            }
            catch (Exception ex)
            {
                txtLotteryStatus.Text = "Error: " + ex.Message;
            }
        
        }

        /// <summary>
        /// Remove the rows in Lottery Data that match the value in the column specified
        /// </summary>
        /// <param name="columnName">column name to look through</param>
        /// <param name="value">value to look for</param>
        private void removeRows(string columnName, string value)
        {
            //Itterate through the dataset from decrementing
            DataTable dtLotteryData = dsLotteryData.Tables[LOTTERYDATA];
            for (int i = dtLotteryData.Rows.Count - 1; i >= 0; i--) 
            {
                //remvoe the row if the value matchs
                DataRow row = dtLotteryData.Rows[i];
                if (row[columnName].ToString() == value)
                {
                    row.Delete();
                }
            }

            //Set the upper bound of the random number
            _upperBound = dgvLotteryData.RowCount - 1;

            //display the total tickets
            lblCount.Text = _upperBound.ToString();
        }
        #endregion
        #endregion
        #endregion
    }
}
